﻿using B.S.Filtration.Domain.到货检验;
using B.S.Filtration.ErrorCode;
using B.S.Filtration.Read.Api.Application.Command.到货检验.BasicInfoCommand;

using B.S.Filtration.Read.Api.Application.Command.到货检验.PurchaseInfoCommand;
using B.S.Filtration.Read.Api.Application.Command.到货检验.PurchaseOrderCommand;
using B.S.Filtration.Read.Api.Application.Command.到货检验.QualityLnspectionCommand;
using B.S.Filtration.Read.Api.Application.Command.到货检验.QualityReportWorkCommand;
using MediatR;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;

namespace B.S.Filtration.Read.Api.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ArrivalController : ControllerBase
    {
        private readonly IMediator mediator;
        private readonly ISqlSugarClient db;
        public ArrivalController(IMediator mediator, ISqlSugarClient db)
        {
            this.mediator = mediator;
            this.db = db;
        }
        /// <summary>
        /// 到货信息基本信息反填
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<List<BasicInfo>>> FTBasicInfo([FromQuery] FTBasicInfoCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 到货信息基本信息显示
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<APIPaging<BasicInfo>>> GetBasicInfo([FromQuery] GetBasicInfoCommand request)
        {
            return await mediator.Send(request);
        }

        /// <summary>
        /// 到货质检信息导出方法
        /// </summary>
        /// <returns>返回任务</returns>
        [HttpGet]
        public ActionResult ExportToExcel()
        {
            try
            {
                // 模拟查询数据，这里假设查询 User 表
                var data = db.Queryable<BasicInfo>().OrderBy(x => x.InspectionDate).ToList();

                // 创建一个新的 Excel 工作簿
                IWorkbook wb = new XSSFWorkbook();

                //在Excel文件中创建工作表
                ISheet sheet = wb.CreateSheet("到货检验信息");

                //在工作表中创建行(第一行,标题);
                IRow row = sheet.CreateRow(0);

                //创建单元格
                row.CreateCell(0).SetCellValue("主键");
                row.CreateCell(1).SetCellValue("检验主题");
                row.CreateCell(2).SetCellValue("检验单号");
                row.CreateCell(3).SetCellValue("检验日期");
                row.CreateCell(4).SetCellValue("检验人员");
                row.CreateCell(5).SetCellValue("所在部门");
                row.CreateCell(6).SetCellValue("联系方式");
                row.CreateCell(7).SetCellValue("采购单号");
                row.CreateCell(8).SetCellValue("采购日期");
                row.CreateCell(9).SetCellValue("供应商名称");
                row.CreateCell(10).SetCellValue("联系人");
                row.CreateCell(11).SetCellValue("联系电话");
                row.CreateCell(12).SetCellValue("采购人员");
                row.CreateCell(13).SetCellValue("采购部门");
                row.CreateCell(14).SetCellValue("联系方式");
                row.CreateCell(15).SetCellValue("关联项目");
                row.CreateCell(16).SetCellValue("备注");

                var index = 1;
                foreach (var item in data)
                {
                    row = sheet.CreateRow(index);

                    row.CreateCell(0).SetCellValue(item.BasicInfoid.ToString());
                    row.CreateCell(1).SetCellValue(item.InspectionSubject);
                    row.CreateCell(2).SetCellValue(item.InspectionOrderNumber);
                    row.CreateCell(3).SetCellValue(item.InspectionDate.ToString("yyyy-MM-dd"));
                    row.CreateCell(4).SetCellValue(item.InspectionPersonnel);
                    row.CreateCell(5).SetCellValue(item.Department);
                    row.CreateCell(6).SetCellValue(item.ContactInformation);
                    row.CreateCell(7).SetCellValue(item.PurchaseOrderNumber);
                    row.CreateCell(8).SetCellValue(item.PurchaseDate.ToString("yyyy-MM-dd"));
                    row.CreateCell(9).SetCellValue(item.SupplierName);
                    row.CreateCell(10).SetCellValue(item.ContactPerson);
                    row.CreateCell(11).SetCellValue(item.ContactPhone);
                    row.CreateCell(12).SetCellValue(item.PurchasingPersonnel);
                    row.CreateCell(13).SetCellValue(item.ContactMethod);
                    row.CreateCell(14).SetCellValue(item.RelatedProject);
                    row.CreateCell(15).SetCellValue(item.Remarks);
                    row.CreateCell(16).SetCellValue(item.IsDel ? "删除" : "未删除");

                    index++;
                }

                //Excel的值
                byte[] buffer;
                //通过内存流读出Excel内容,转换为二进制数组
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }

                //导出Excel  using  Microsoft.AspNetCore.StaticFiles;
                string excelMime = new FileExtensionContentTypeProvider().Mappings[".xlsx"];

                //导出
                return File(buffer, excelMime, "到货质检信息表.xlsx");
            }
            catch (Exception ex)
            {
                // 处理异常
                Console.WriteLine($"导出 Excel 时出错: {ex.Message}");
            }

            return new EmptyResult();
        }
        /// <summary>
        /// 到货质检信息导入方法
        /// </summary>
        /// <param name="file"></param>
        /// <returns>返回任务</returns>
        [HttpPost]
        public IActionResult ImportExcel(IFormFile file)
        {
            try
            {
                if (file != null && file.Length > 0)
                {

                    // 读取 Excel 文件
                    using (var stream = new MemoryStream())
                    {
                        file.CopyTo(stream);
                        stream.Position = 0;
                        using (var workbook = new XSSFWorkbook(stream))
                        {
                            var sheet = workbook.GetSheetAt(0);
                            var production = new List<BasicInfo>();

                            // 从第二行开始读取数据，跳过表头
                            for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                            {
                                var row = sheet.GetRow(rowIndex);
                                if (row != null)
                                {
                                    var user = new BasicInfo
                                    {
                                        InspectionSubject = GetCellValueAsString(row.GetCell(1)),
                                        InspectionOrderNumber = GetCellValueAsString(row.GetCell(2)),
                                        InspectionDate = GetCellValueAsDateTime(row.GetCell(3)),
                                        InspectionPersonnel = GetCellValueAsString(row.GetCell(4)),
                                        Department = GetCellValueAsString(row.GetCell(5)),
                                        ContactInformation = GetCellValueAsString(row.GetCell(6)),
                                        PurchaseOrderNumber = GetCellValueAsString(row.GetCell(7)),
                                        PurchaseDate = GetCellValueAsDateTime(row.GetCell(8)),
                                        SupplierName = GetCellValueAsString(row.GetCell(9)),
                                        ContactPerson = GetCellValueAsString(row.GetCell(10)),
                                        ContactPhone = GetCellValueAsString(row.GetCell(11)),
                                        PurchasingPersonnel = GetCellValueAsString(row.GetCell(12)),
                                        PurchasingDepartment = GetCellValueAsString(row.GetCell(13)),
                                        ContactMethod = GetCellValueAsString(row.GetCell(14)),
                                        RelatedProject = GetCellValueAsString(row.GetCell(15)),
                                        Remarks = GetCellValueAsString(row.GetCell(16)),
                                        IsDel = GetCellValueAsBoolean(row.GetCell(17)),
                                    };
                                    production.Add(user);
                                }
                            }

                            // 批量插入数据到数据库
                            db.Insertable(production).ExecuteCommand();
                        }
                    }

                    return Ok("导入成功");
                }
                return BadRequest("请选择要导入的 Excel 文件");
            }
            catch (Exception ex)
            {
                return StatusCode(500, $"导入失败: {ex.Message}");
            }
        }
        //导入int类型数据转换
        private int GetCellValueAsInt(ICell cell)
        {
            if (cell == null)
            {
                return 0;
            }
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    return (int)cell.NumericCellValue;
                case CellType.String:
                    if (int.TryParse(cell.StringCellValue, out int result))
                    {
                        return result;
                    }
                    return 0;
                default:
                    return 0;
            }
        }

        //导入datetime类型转换
        private DateTime GetCellValueAsDateTime(ICell cell)
        {
            if (cell == null)
            {
                return DateTime.MinValue;
            }
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
                    {
                        // 新增 ?? DateTime.MinValue 处理 null 情况
                        return cell.DateCellValue ?? DateTime.MinValue;
                    }
                    return DateTime.MinValue;
                case CellType.String:
                    if (DateTime.TryParse(cell.StringCellValue, out DateTime result))
                    {
                        return result;
                    }
                    return DateTime.MinValue;
                default:
                    return DateTime.MinValue;
            }
        }

        //导入bool类型转换
        private bool GetCellValueAsBoolean(ICell cell)
        {
            if (cell == null)
            {
                return false;
            }
            switch (cell.CellType)
            {
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.String:
                    string value = cell.StringCellValue.Trim().ToLower();
                    if (value == "true" || value == "删除" || value == "已生成" || value == "自动生成订单")
                    {
                        return true;
                    }
                    else if (value == "false" || value == "未删除" || value == "未生成")
                    {
                        return false;
                    }
                    return false;
                default:
                    return false;
            }
        }

        //导入string类型数据转换
        private string GetCellValueAsString(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Formula:
                    return cell.CellFormula;
                default:
                    return string.Empty;
            }
        }
        /// <summary>
        /// 到货信息明细信息显示
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<APIPaging<PurchaseInfo>>> GetPurchaseInfo([FromQuery] GetPurchaseInfoCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 到货信息明细信息反填
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<List<PurchaseInfo>>> FTPurchaseInfo([FromQuery] FTPurchaseInfoCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 到货信息明细信息导出方法
        /// </summary>
        /// <returns>返回任务</returns>
        [HttpGet]
        public ActionResult ExportToExce2()
        {
            try
            {
                // 模拟查询数据，这里假设查询 User 表
                var data = db.Queryable<PurchaseInfo>().OrderBy(x => x.PurchaseInfoid).ToList();

                // 创建一个新的 Excel 工作簿
                IWorkbook wb = new XSSFWorkbook();

                //在Excel文件中创建工作表
                ISheet sheet = wb.CreateSheet("到货质检明细表");

                //在工作表中创建行(第一行,标题);
                IRow row = sheet.CreateRow(0);

                //创建单元格
                row.CreateCell(0).SetCellValue("主键");
                row.CreateCell(1).SetCellValue("物品编码");
                row.CreateCell(2).SetCellValue("物品名称");
                row.CreateCell(3).SetCellValue("规格型号");
                row.CreateCell(4).SetCellValue("单位");
                row.CreateCell(5).SetCellValue("品牌");
                row.CreateCell(6).SetCellValue("采购数量");
                row.CreateCell(7).SetCellValue("检验类型");
                row.CreateCell(8).SetCellValue("收货数量");
                row.CreateCell(9).SetCellValue("检验数量");
                row.CreateCell(10).SetCellValue("合格数量");
                row.CreateCell(11).SetCellValue("可入库数量");
                row.CreateCell(12).SetCellValue("退货数量");
                row.CreateCell(13).SetCellValue("备注");
                row.CreateCell(14).SetCellValue("信息表");
                row.CreateCell(15).SetCellValue("删除");
                row.CreateCell(16).SetCellValue("备注");

                var index = 1;
                foreach (var item in data)
                {
                    row = sheet.CreateRow(index);

                    row.CreateCell(0).SetCellValue(item.PurchaseInfoid.ToString());
                    row.CreateCell(1).SetCellValue(item.ItemCode);
                    row.CreateCell(2).SetCellValue(item.ItemName);
                    row.CreateCell(3).SetCellValue(item.SpecificationModel);
                    row.CreateCell(4).SetCellValue(item.Unit);
                    row.CreateCell(5).SetCellValue(item.Brand);
                    row.CreateCell(6).SetCellValue(item.PurchaseQuantity);
                    row.CreateCell(7).SetCellValue(item.InspectionType);
                    row.CreateCell(8).SetCellValue(item.ReceivedQuantity);
                    row.CreateCell(9).SetCellValue(item.InspectionQuantity);
                    row.CreateCell(10).SetCellValue(item.Qualifiedquantity);
                    row.CreateCell(11).SetCellValue(item.InboundQuantity);
                    row.CreateCell(12).SetCellValue(item.ReturnQuantity);
                    row.CreateCell(13).SetCellValue(item.Remarks);
                    row.CreateCell(14).SetCellValue(item.BasicInfoid);
                    row.CreateCell(15).SetCellValue(item.Remarks);
                    row.CreateCell(16).SetCellValue(item.IsDel ? "删除" : "未删除");

                    index++;
                }

                //Excel的值
                byte[] buffer;
                //通过内存流读出Excel内容,转换为二进制数组
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }

                //导出Excel  using  Microsoft.AspNetCore.StaticFiles;
                string excelMime = new FileExtensionContentTypeProvider().Mappings[".xlsx"];

                //导出
                return File(buffer, excelMime, "到货质检信息表.xlsx");
            }
            catch (Exception ex)
            {
                // 处理异常
                Console.WriteLine($"导出 Excel 时出错: {ex.Message}");
            }

            return new EmptyResult();
        }
        /// <summary>
        /// 到货信息明细信息导入方法
        /// </summary>
        /// <param name="file"></param>
        /// <returns>返回任务</returns>
        [HttpPost]
        public IActionResult ImportExce2(IFormFile file)
        {
            try
            {
                if (file != null && file.Length > 0)
                {

                    // 读取 Excel 文件
                    using (var stream = new MemoryStream())
                    {
                        file.CopyTo(stream);
                        stream.Position = 0;
                        using (var workbook = new XSSFWorkbook(stream))
                        {
                            var sheet = workbook.GetSheetAt(0);
                            var production = new List<PurchaseInfo>();

                            // 从第二行开始读取数据，跳过表头
                            for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                            {
                                var row = sheet.GetRow(rowIndex);
                                if (row != null)
                                {
                                    var user = new PurchaseInfo
                                    {
                                        ItemCode = GetCellValueAsString(row.GetCell(1)),
                                        ItemName = GetCellValueAsString(row.GetCell(2)),
                                        SpecificationModel = GetCellValueAsString(row.GetCell(3)),
                                        Unit = GetCellValueAsString(row.GetCell(4)),
                                        Brand = GetCellValueAsString(row.GetCell(5)),
                                        PurchaseQuantity = GetCellValueAsInt(row.GetCell(6)),
                                        InspectionType = GetCellValueAsString(row.GetCell(7)),
                                        ReceivedQuantity = GetCellValueAsInt(row.GetCell(8)),
                                        InspectionQuantity = GetCellValueAsInt(row.GetCell(9)),
                                        Qualifiedquantity = GetCellValueAsInt(row.GetCell(10)),
                                        InboundQuantity = GetCellValueAsInt(row.GetCell(11)),
                                        ReturnQuantity = GetCellValueAsInt(row.GetCell(12)),
                                        Remarks = GetCellValueAsString(row.GetCell(13)),
                                        BasicInfoid = GetCellValueAsInt(row.GetCell(14)),
                                        IsDel = GetCellValueAsBoolean(row.GetCell(15)),
                                    };
                                    production.Add(user);
                                }
                            }

                            // 批量插入数据到数据库
                            db.Insertable(production).ExecuteCommand();
                        }
                    }

                    return Ok("导入成功");
                }
                return BadRequest("请选择要导入的 Excel 文件");
            }
            catch (Exception ex)
            {
                return StatusCode(500, $"导入失败: {ex.Message}");
            }
        }
        /// <summary>
        /// 质检反填
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<List<QualityLnspection>>> FTQualityLnspection([FromQuery] FTQualityLnspectionCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 质检显示
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<APIPaging<QualityLnspection>>> GetQualityLnspection([FromQuery] GetQualityLnspectionCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 采购信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<List<PurchaseOrder>>> GetPurchaseOrder([FromQuery] GetPurchaseOrderCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 质检报工反填
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<List<QualityReportWork>>> FTQualityReportWork([FromQuery] FTQualityReportWorkCommand request)
        {
            return await mediator.Send(request);
        }
        /// <summary>
        /// 质检报工显示
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<APIResult<APIPaging<QualityReportWork>>> GetQualityReportWork([FromQuery] GetQualityReportWorkCommand request)
        {
            return await mediator.Send(request);
        }

    }
}
